﻿namespace LinqToSqlQueryVisualizer
{
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Globalization;

    internal static class QueryExecution
    {
        // reconstructs an object from its Clr type and the value string
        // (which was obtained as a result of calling the ToString method)

        // Execute queries using the original information about the query
        // This method constructs the query and parameters as in Linq to SQL
        // (not using the parameter values as part of the Sql text)
        internal static void ExecuteOriginalQueries(DataSet ds1, DataSet ds2, SqlQueryText[] infos, string connectionString)
        {
            var connection = new SqlConnection(connectionString);

            // retrieve data
            using (connection)
            {
                connection.Open();

                var cmd1 = GetSqlCommand(infos[0], connection);
                var adapter1 = new SqlDataAdapter(cmd1);
                adapter1.Fill(ds1);

                if (infos.Length <= 1)
                {
                    return;
                }

                var cmd2 = GetSqlCommand(infos[1], connection);
                var adapter2 = new SqlDataAdapter(cmd2);
                adapter2.Fill(ds2);
            }
        }

        // Executes Sql commands as text
        internal static void ExecuteQueries(DataSet ds1, DataSet ds2, string cmd1, string cmd2, string connectionString)
        {
            var conn = new SqlConnection(connectionString);
            using (conn)
            {
                conn.Open();
                var sqlCmd1 = new SqlCommand(cmd1, conn);
                var adapter1 = new SqlDataAdapter(sqlCmd1);
                adapter1.Fill(ds1);

                var sqlCmd2 = new SqlCommand(cmd2, conn);
                var adapter2 = new SqlDataAdapter(sqlCmd2);
                adapter2.Fill(ds2);
            }
        }

        internal static void ExecuteQuery(DataSet ds1, string cmd1, string connectionString)
        {
            var conn = new SqlConnection(connectionString);
            using (conn)
            {
                conn.Open();
                var sqlCmd1 = new SqlCommand(cmd1, conn);
                var adapter1 = new SqlDataAdapter(sqlCmd1);
                adapter1.Fill(ds1);
            }
        }

        private static object GetObject(string val, string sqlType)
        {
            switch ((DbType)Enum.Parse(typeof(DbType), sqlType.Trim()))
            {
                case DbType.String:
                    return val;
                case DbType.Int16:
                    return Int16.Parse(val, CultureInfo.CurrentUICulture);
                case DbType.Int32:
                    return Int32.Parse(val, CultureInfo.CurrentUICulture);
                case DbType.Int64:
                    return Int64.Parse(val, CultureInfo.CurrentUICulture);
                case DbType.Byte:
                    return Byte.Parse(val, CultureInfo.CurrentUICulture);
                case DbType.Double:
                    return Double.Parse(val, CultureInfo.CurrentUICulture);
                case DbType.Single:
                    return Single.Parse(val, CultureInfo.CurrentUICulture);
                case DbType.Decimal:
                    return Decimal.Parse(val, CultureInfo.CurrentUICulture);
                case DbType.Boolean:
                    return Boolean.Parse(val);
                case DbType.DateTime:
                    return DateTime.Parse(val, CultureInfo.CurrentUICulture);
                case DbType.Time:
                    return TimeSpan.Parse(val);
                case DbType.Guid:
                    return new Guid(val);
                default:
                    throw new NotSupportedException("Type " + sqlType + " is not supported for parameters in Linq to Sql Query Visualizer");
            }
        }

        /// <summary>
        /// Create a SqlCommand by creating parameters from the strings in qt.Params
        /// and using the text in qt.Text.
        /// </summary>
        /// <param name="qt">SqlQueryText input.</param>
        /// <param name="conn">SqlConnection to associate with the SqlCommand.</param>
        /// <returns>A SqlCommand.</returns>
        private static SqlCommand GetSqlCommand(SqlQueryText qt, SqlConnection conn)
        {
            var cmd = new SqlCommand(qt.Text, conn);
            foreach (var param in qt.Params)
            {
                var sqlParam = cmd.CreateParameter();
                sqlParam.ParameterName = param.Name;
                var val = GetObject(param.Value, param.SqlType);
                sqlParam.Value = val;
                cmd.Parameters.Add(sqlParam);
            }

            return cmd;
        }
    }
}